# -*- coding: utf-8 -*-
"""
Created on Tue Mar 19 11:14:35 2019

@author: Simon TIÈCHE - simon@simontieche.com
"""

# Import `os` 
import os

# Retrieve current working directory (`cwd`)
cwd = os.getcwd()
cwd

# Change directory 
os.chdir('XXXXXXXXXXXXXXXXXXXXXXX')

# List all files and directories in current directory
os.listdir('.')

# Import `load_workbook` module from `openpyxl`
from openpyxl import load_workbook

### Equity indices
file = 'equity_indices'

# Load in the workbook
wb = load_workbook(file + '.xlsx')

# Get sheet names
print(wb.sheetnames)

sheetlist = wb.sheetnames

for sheetname in sheetlist:
    print(sheetname)

    # Get a sheet by name 
    sheet = wb[sheetname]

    sheet.insert_rows(6, amount=1)

    # Write to the sheet of the workbook 
    sheet['A6'] = 'date'
    sheet['B6'] = 'usdmsci_' + sheetname
    sheet['C6'] = 'usdreturn_' + sheetname
    sheet['D6'] =  'usdpe_' + sheetname
    sheet['E6'] = 'usddy_' + sheetname
    sheet['F6'] = 'mvalue_' + sheetname
    sheet['G6'] = 'msci_' + sheetname
    sheet['H6'] = 'return_' + sheetname
    sheet['I6'] = 'pe_' + sheetname
    sheet['J6'] = 'dy_' + sheetname
    sheet.delete_cols(11, amount=1)

wb.save(file + '_clean.xlsx')